package com.lee.common.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;
import com.jacob.com.Variant;

//import freemarker.template.Configuration;

public class MakeExcel {
	private static ActiveXComponent xl = null; // Excel对象(防止打开多个)
	private static Dispatch workbooks = null; // 工作簿对象
	private Dispatch workbook = null; // 具体工作簿
	private Dispatch sheets = null;// 获得sheets集合对象
	private Dispatch currentSheet = null;// 当前sheet
	// private Configuration configuration = null;
	private static final int wdFormatPDF = 17;
	private static final int xlTypePDF = 0;
	private static final int ppSaveAsPDF = 32;

	// private static final int msoTrue = -1;
	// private static final int msofalse = 0;

	public static void main(String[] args) throws Exception {
		MakeExcel ctp = new MakeExcel();
		System.out.print(ctp.getCellPosition(6, 5));
		// ctp.OpenExcel("d:/excel/a.xlsx", false);
		// ctp.SaveAs("D:excel/b.xlsx");
		// ctp.CloseExcel(false);
		// ctp.releaseSource();
		// ctp.createWord();
		ExprotExcel(getStudent(), "学生表", "e:\12312312112.xls");
	}

	public void OpenExcel(String filepath, boolean visible) {
		try {
			initComponents(); // 清空原始变量
			ComThread.InitSTA();
			if (xl == null)
				xl = new ActiveXComponent("Excel.Application"); // Excel对象
			xl.setProperty("Visible", new Variant(visible));// 设置是否显示打开excel
			if (workbooks == null)
				workbooks = xl.getProperty("Workbooks").toDispatch(); // 打开具体工作簿
			workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method, new Object[] { filepath, new Variant(false), // 是否以只读方式打开
					new Variant(true), "1", "pwd" }, // 输入密码"pwd",若有密码则进行匹配，无则直接打开
					new int[1]).toDispatch();
		} catch (Exception e) {
			e.printStackTrace();
			releaseSource();
		}
	}

	/**
	 * 工作簿另存为
	 * 
	 * @param filePath
	 *            另存为的路径 例如 SaveAs="D:TEST/c.xlsx"
	 */
	public void SaveAs(String filePath) {
		File file = new File(filePath);
		if (file.exists()) {
			file.delete();
		}
		Dispatch.call(workbook, "SaveAs", filePath);
	}

	/**
	 * 关闭excel文档
	 * 
	 * @param f
	 *            含义不明 （关闭是否保存？默认false）
	 */
	public void CloseExcel(boolean f) {
		try {
			// Dispatch.call(workbook, "Save");
			Dispatch.call(workbook, "Close", new Variant(f));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			releaseSource();
		}
	}

	/*
	 * 初始化
	 */
	private void initComponents() {
		workbook = null;
		currentSheet = null;
		sheets = null;
	}

	/**
	 * 释放资源
	 */
	private void releaseSource() {
		if (xl != null) {
			xl.invoke("Quit", new Variant[] {});
			xl = null;
		}
		workbooks = null;
		ComThread.Release();
		System.gc();
	}

	/**
	 * 得到当前sheet
	 * 
	 * @return
	 */
	public Dispatch getCurrentSheet() {
		currentSheet = Dispatch.get(workbook, "ActiveSheet").toDispatch();
		return currentSheet;
	}

	/**
	 * 修改当前工作表的名字
	 * 
	 * @param newName
	 */

	public void modifyCurrentSheetName(String newName) {
		Dispatch.put(getCurrentSheet(), "name", newName);
	}

	/**
	 * 得到当前工作表的名字
	 * 
	 * @return
	 */
	private String getCurrentSheetName(Dispatch sheets) {
		return Dispatch.get(sheets, "name").toString();
	}

	/**
	 * 通过工作表名字得到工作表
	 * 
	 * @param name
	 *            sheetName
	 * @return
	 */

	public Dispatch getSheetByName(String name) {
		return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, new Object[] { name }, new int[1]).toDispatch();
	}

	/**
	 * 得到sheets的集合对象
	 * 
	 * @return
	 */
	private Dispatch getSheets() {
		if (sheets == null)
			sheets = Dispatch.get(workbook, "sheets").toDispatch();
		return sheets;
	}

	/**
	 * 通过工作表索引得到工作表(第一个工作簿index为1)
	 * 
	 * @param index
	 * @return sheet对象
	 */
	private Dispatch getSheetByIndex(Integer index) {
		return Dispatch.invoke(getSheets(), "Item", Dispatch.Get, new Object[] { index }, new int[1]).toDispatch();
	}

	/**
	 * 得到sheet的总数
	 * 
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public int getSheetCount() {
		int count = Dispatch.get(getSheets(), "count").toInt();
		return count;
	}

	/**
	 * 给所有的sheet添加背景
	 * 
	 * @param filepath
	 *            图片路径
	 */
	public void setBlackGroudPrituce(String filepath) {
		int num = this.getSheetCount();
		for (int i = 1; i <= num; i++) {
			Dispatch sheets = this.getSheetByIndex(i);
			Dispatch.call(sheets, "SetBackgroundPicture", filepath);
		}
	}

	/**
	 * 添加新的工作表(sheet)，并且隐藏（添加后为默认为当前激活的工作表）
	 */
	public void addSheet(String name) {
		// for (int i = 1; i <= this.getSheetCount(); i++) {
		// Dispatch sheets=this.getSheetByIndex(i);
		// if(name.equals(this.getCurrentSheetName(sheets)))
		// {
		// return false;
		// }
		// }
		currentSheet = Dispatch.get(Dispatch.get(workbook, "sheets").toDispatch(), "add").toDispatch();
		// Dispatch.put(currentSheet,"Name",name);
		Dispatch.put(currentSheet, "Visible", new Boolean(false));
		System.out.println("插入信息为:" + name);
	}

	/**
	 * 得到工作薄的名字
	 * 
	 * @return
	 */

	public String getWorkbookName() {
		if (workbook == null)
			return null;
		return Dispatch.get(workbook, "name").toString();
	}

	/**
	 * 获取所有表名
	 */
	public List<String> findSheetName() {
		int num = this.getSheetCount();
		List<String> list = new ArrayList<String>();
		for (int i = 1; i <= num; i++) {
			currentSheet = this.getSheetByIndex(i);
			list.add(this.getCurrentSheetName(currentSheet));
		}
		return list;
	}

	/**
	 * 设置页脚信息
	 */

	public void setFooter(String foot) {
		currentSheet = this.getCurrentSheet();
		Dispatch PageSetup = Dispatch.get(currentSheet, "PageSetup").toDispatch();
		Dispatch.put(PageSetup, "CenterFooter", foot);
	}

	/**
	 * 获取页脚信息
	 */

	public String getFooter() {
		currentSheet = this.getCurrentSheet();
		Dispatch PageSetup = Dispatch.get(currentSheet, "PageSetup").toDispatch();
		return Dispatch.get(PageSetup, "CenterFooter").toString();
	}

	/**
	 * 锁定工作簿
	 */

	public void setPassword() {
		Dispatch.call(workbook, "Protect", 123, true, false);
	}

	/**
	 * 设置名称管理器
	 * 
	 * @param name
	 *            名称管理器名 不能以数字或者下划线开头，中间不能包含空格和其他无效字符
	 * @param comment
	 *            备注
	 * @param place
	 *            备注位置
	 * @return
	 */
	public void setName(String name, String place, String comment) {
		Dispatch Names = Dispatch.get(workbook, "Names").toDispatch();
		Dispatch.call(Names, "Add", name, place, false).toDispatch();
		Dispatch.put(Names, "Comment", comment); // 插入备注
	}

	/**
	 * 获取名称管理器
	 * 
	 * @param name
	 *            名称管理器名
	 * @return
	 */
	public String getName(String name) {
		Dispatch Names = Dispatch.get(workbook, "Names").toDispatch();
		Dispatch Name = Dispatch.call(Names, "Item", name).toDispatch();
		return Dispatch.get(Name, "Value").toString();
	}

	/**
	 * 单元格写入值
	 * 
	 * @param sheet
	 *            被操作的sheet
	 * @param position
	 *            单元格位置，如：C1
	 * @param type
	 *            值的属性 如：value
	 * @param value
	 */

	public void setValue(String position, Object value) {
		currentSheet = this.getCurrentSheet();
		Dispatch cell = Dispatch.invoke(currentSheet, "Range", Dispatch.Get, new Object[] { position }, new int[1]).toDispatch();
		Dispatch.put(cell, "Value", value);
		String color = this.getColor(cell);
		this.setFont(cell, color);
	}

	/**
	 * 设置字体
	 */
	private void setFont(Dispatch cell, String color) {
		Dispatch font = Dispatch.get(cell, "Font").toDispatch();
		// Dispatch.put(font,"FontStyle", "Bold Italic");
		Dispatch.put(font, "size", "1");
		Dispatch.put(font, "color", color);
	}

	/**
	 * 获取背景颜色
	 */
	private String getColor(Dispatch cell) {
		Dispatch Interior = Dispatch.get(cell, "Interior").toDispatch();
		String color = Dispatch.get(Interior, "color").toString();
		return color;
	}

	/**
	 * 单元格读取值
	 * 
	 * @param position
	 *            单元格位置，如： C1
	 * @param sheet
	 * @return
	 */

	public Variant getValue(String position) {
		currentSheet = this.getCurrentSheet();
		Dispatch cell = Dispatch.invoke(currentSheet, "Range", Dispatch.Get, new Object[] { position }, new int[1]).toDispatch();
		Variant value = Dispatch.get(cell, "Value");
		return value;
	}

	/**
	 * 获取最大行数
	 * 
	 * @return
	 */

	public int getRowCount() {
		currentSheet = this.getCurrentSheet();
		Dispatch UsedRange = Dispatch.get(currentSheet, "UsedRange").toDispatch();
		Dispatch rows = Dispatch.get(UsedRange, "Rows").toDispatch();
		int num = Dispatch.get(rows, "count").getInt();
		return num;
	}

	/**
	 * 获取最大列数
	 * 
	 * @return
	 */

	public int getColumnCount() {
		currentSheet = this.getCurrentSheet();
		Dispatch UsedRange = Dispatch.get(currentSheet, "UsedRange").toDispatch();
		Dispatch Columns = Dispatch.get(UsedRange, "Columns").toDispatch();
		int num = Dispatch.get(Columns, "count").getInt();
		return num;
	}

	/**
	 * 获取位置
	 * 
	 * @param rnum
	 *            最大行数
	 * @param cnum
	 *            最大列数
	 */
	private String getCellPosition(int rnum, int cnum) {
		String cposition = "";
		if (cnum > 26) {
			int multiple = (cnum) / 26;
			int remainder = (cnum) % 26;
			char mchar = (char) (multiple + 64);
			char rchar = (char) (remainder + 64);
			cposition = mchar + "" + rchar;
		} else {
			cposition = (char) (cnum + 64) + "";
		}
		cposition += rnum;
		return cposition;
	}

	/**
	 * 取消兼容性检查，在保存或者另存为时改检查会导致弹窗
	 */

	public void setCheckCompatibility() {
		Dispatch.put(workbook, "CheckCompatibility", false);
	}

	/**
	 * 为每个表设置打印区域
	 */

	public void setPrintArea() {
		int count = Dispatch.get(sheets, "count").changeType(Variant.VariantInt).getInt();
		for (int i = count; i >= 1; i--) {
			Dispatch sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get, new Object[] { i }, new int[1]).toDispatch();
			Dispatch page = Dispatch.get(sheet, "PageSetup").toDispatch();
			Dispatch.put(page, "PrintArea", false);
			Dispatch.put(page, "Orientation", 2);
			Dispatch.put(page, "Zoom", false); // 值为100或false
			Dispatch.put(page, "FitToPagesTall", false); // 所有行为一页
			Dispatch.put(page, "FitToPagesWide", 1); // 所有列为一页(1或false)
		}
	}

	@SuppressWarnings("rawtypes")
	public static ArrayList getStudent() throws Exception {
		ArrayList<String[]> list = new ArrayList<String[]>();
		String[] user1 = new String[] { "张三", "男" };
		String[] user2 = new String[] { "李四", "女" };
		String[] user3 = new String[] { "王五", "女" };
		list.add(user1);
		list.add(user2);
		list.add(user3);
		return list;
	}

	/**
	 * 向excel中添加数据
	 * 
	 * @param list
	 *            所填充数据(包括表头)
	 * @param title
	 *            表名
	 * @param fathPath
	 *            表格另存路径
	 * @throws Exception
	 */
	@SuppressWarnings({ "rawtypes" })
	public static void ExprotExcel(ArrayList list, String title, String fathPath) throws Exception {
		// 第一步，创建一个webbook，对应一个Excel文件
		HSSFWorkbook wb = new HSSFWorkbook();
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(title);
		sheet.setDefaultColumnWidth((int) 30);
		sheet.setColumnWidth((int) 0, (short) 2000);
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		HSSFCellStyle styleTitle = wb.createCellStyle();
		styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		styleTitle.setBorderBottom((short) 1);
		styleTitle.setBorderTop((short) 1);
		styleTitle.setBorderRight((short) 1);
		styleTitle.setBorderLeft((short) 1);
		styleTitle.setFillBackgroundColor(HSSFColor.BLUE.index);
		// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
		for (int i = 0; i < list.size(); i++) {
			HSSFRow row = sheet.createRow((int) i);
			if (i == 0) {
				row.setHeight((short) 400);
			}
			String[] obj = (String[]) list.get(i);

			for (int j = 0; j < obj.length; j++) {
				HSSFCell cell = row.createCell((int) j);
				cell.setCellValue(obj[j]);
				if (i == 0) {
					cell.setCellStyle(styleTitle);
				} else {
					cell.setCellStyle(style);
				}
			}
		}
		sheet.autoSizeColumn(1);
		// 第六步，将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream(fathPath);
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 把文件转换成pdf格式
	 */
	public static boolean convert2PDF(String inputFile, String pdfFile) {
		String suffix = getFileSufix(inputFile);
		File file = new File(inputFile);
		if (!file.exists()) {
			System.out.println("文件不存在！");
			return false;
		}
		if (suffix.equals("pdf")) {
			System.out.println("PDF not need to convert!");
			return false;
		}
		if (suffix.equals("doc") || suffix.equals("docx") || suffix.equals("txt")) {
			return word2PDF(inputFile, pdfFile);
		} else if (suffix.equals("ppt") || suffix.equals("pptx")) {
			return ppt2PDF(inputFile, pdfFile);
		} else if (suffix.equals("xls") || suffix.equals("xlsx")) {
			return excel2PDF(inputFile, pdfFile);
		} else {
			System.out.println("文件格式不支持转换!");
			return false;
		}
	}

	/**
	 * 获取文件后缀
	 */
	public static String getFileSufix(String fileName) {
		int splitIndex = fileName.lastIndexOf(".");
		return fileName.substring(splitIndex + 1);
	}

	/**
	 * 把word转换成pdf
	 * 
	 * @param inputFile
	 *            :word路径
	 * @param pdfFile
	 *            ：转换成的pdf输出路径
	 */
	public static boolean word2PDF(String inputFile, String pdfFile) {
		try {
			// 打开word应用程序
			ActiveXComponent app = new ActiveXComponent("Word.Application");
			// 设置word不可见
			app.setProperty("Visible", false);
			// 获得word中所有打开的文档,返回Documents对象
			Dispatch docs = app.getProperty("Documents").toDispatch();
			// 调用Documents对象中Open方法打开文档，并返回打开的文档对象Document
			Dispatch doc = Dispatch.call(docs, "Open", inputFile, false, true).toDispatch();
			// 调用Document对象的SaveAs方法，将文档保存为pdf格式
			/*
			 * Dispatch.call(doc, "SaveAs", pdfFile, wdFormatPDF
			 * //word保存为pdf格式宏，值为17 );
			 */
			Dispatch.call(doc, "ExportAsFixedFormat", pdfFile, wdFormatPDF // word保存为pdf格式宏，值为17
			);
			// 关闭文档
			Dispatch.call(doc, "Close", false);
			// 关闭word应用程序
			app.invoke("Quit", 0);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	/**
	 * 把excel转换成pdf
	 * 
	 * @param inputFile
	 *            :word路径
	 * @param pdfFile
	 *            ：转换成的pdf输出路径
	 */
	public static boolean excel2PDF(String inputFile, String pdfFile) {
		try {
			ActiveXComponent app = new ActiveXComponent("Excel.Application");
			app.setProperty("Visible", false);
			Dispatch excels = app.getProperty("Workbooks").toDispatch();
			Dispatch excel = Dispatch.call(excels, "Open", inputFile, false, true).toDispatch();
			Dispatch.call(excel, "ExportAsFixedFormat", xlTypePDF, pdfFile);
			Dispatch.call(excel, "Close", false);
			app.invoke("Quit");
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	/**
	 * 把ppt转换成pdf
	 * 
	 * @param inputFile
	 *            :word路径
	 * @param pdfFile
	 *            ：转换成的pdf输出路径
	 */
	public static boolean ppt2PDF(String inputFile, String pdfFile) {
		try {
			ActiveXComponent app = new ActiveXComponent("PowerPoint.Application");
			// app.setProperty("Visible", msofalse);
			Dispatch ppts = app.getProperty("Presentations").toDispatch();

			Dispatch ppt = Dispatch.call(ppts, "Open", inputFile, true,// ReadOnly
					true,// Untitled指定文件是否有标题
					false// WithWindow指定文件是否可见
					).toDispatch();

			Dispatch.call(ppt, "SaveAs", pdfFile, ppSaveAsPDF);
			Dispatch.call(ppt, "Close");
			app.invoke("Quit");
			return true;
		} catch (Exception e) {
			return false;
		}
	}
}
